with tmp_network as (
    select case
               when network_type = 3 then 1 --加盟
               when network_type = 4 then 2 --中心
               when network_type = 5 then 3 --集散
               when network_type = 6 then 4 --网点
               when network_type = 2 then 5 --代理区
               when network_type = 1 then 6 --总部
               else 0
        end            as network_type
         , code
         , name        as network_name     --网点名称
         , agent_code                      --代理区编码
         , agent_name                      --代理区名称
         , fran_code   as franchisee_code  --加盟商编码
         , fran_name   as franchisee_name  --加盟商名称
         , center_code as transcenter_code --转运中心编码
         , center_name as transcenter_name --转运中心名称
    from jms_dim.dim_network_whole_massage
)
insert
overwrite
table
jms_dwd.dwd_electronic_package_list_base_hi
partition
(
dt
)
select tmp_barscan.id
     , tmp_barscan.package_code
     , tmp_barscan.package_name
     , tmp_barscan.package_property
     , tmp_barscan.package_property_name
     , tmp_barscan.package_type
     , tmp_barscan.package_type_name
     , tmp_barscan.center_id
     , tmp_barscan.center_code
     , tmp_barscan.center_name
     , tmp_barscan.package_number
     , tmp_barscan.print_number
     , tmp_barscan.print_times_number
     , tmp_barscan.create_by
     , tmp_barscan.create_by_code
     , tmp_barscan.create_by_name
     , tmp_barscan.create_network_id
     , tmp_barscan.create_network_code
     , tmp_barscan.create_network_name
     , tmp_barscan.create_time
     , tmp_barscan.is_used
     , tmp_barscan.used_time
     , tmp_network.network_type     as network_type
     , tmp_network.agent_code       as site_agent_code
     , tmp_network.agent_name       as site_agent_name
     , tmp_network.franchisee_code  as site_franchisee_code
     , tmp_network.franchisee_name  as site_franchisee_name
     , tmp_network.transcenter_code as site_transcenter_code
     , tmp_network.transcenter_name as site_transcenter_name
     , tmp_barscan.dt               as dt
from jms_ods.electronic_package_list_hi tmp_barscan
         left join tmp_network tmp_network on tmp_barscan.create_network_code = tmp_network.code
where tmp_barscan.dt = '{{ execution_date | cst_hour }}'
;
